﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;

namespace GPSTrackingClientsWebsiteManager.Espanol.Pages
{
    public class controller
    {
        public controller()
        {
            sqlConexion = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["netTiersConnectionString"].ToString());
            
        }

        private SqlConnection sqlConexion;
        private string obtenerRutaHistoricaBD = "select 20120001 Imei,'Rene' Nombre, Latitude, Longitude, 1 Tipo_Vehiculo from track_data where Imei = 123 order by Date_Time asc;";
        
        private string obtenerTodosLosPoligonosBD = "select IdPoly, Username, PolyName from Poly where Username = @Username order by PolyName asc";

        private string obtenerPoligonosDetalleBD = "select NumPos, Latitude, Longitude from PolyDetails where Username = @Username and IdPoly = @IdPoly";
        private string eliminarPoligonoDetalleBD = "delete PolyDetails where Username = @Username and IdPoly = @IdPoly";
        private string eliminarPoligonoBD = "delete Poly where Username = @Username and IdPoly = @IdPoly";

        public string insertCustomer(string customerName, Int64 customerGroupID, float latitude, float longitude)
        {
            string comand = "insert into Customers values('" + customerName + "'," + latitude + "," + longitude + "); SELECT SCOPE_IDENTITY()";

            SqlTransaction myTrasnsaccion;
            sqlConexion.Open();
            myTrasnsaccion = sqlConexion.BeginTransaction();
            SqlCommand cmd;

            string customerID = "-1";
            try
            {
                cmd = new SqlCommand(comand, sqlConexion, myTrasnsaccion);

                customerID = cmd.ExecuteScalar().ToString();

                cmd = new SqlCommand("insert into CustomersByGroups values(" + Int64.Parse(customerID) + "," + customerGroupID + ");", sqlConexion, myTrasnsaccion);

                cmd.ExecuteNonQuery();

                myTrasnsaccion.Commit();
                sqlConexion.Close();
                return customerID;
            }
            catch (Exception e)
            {
                myTrasnsaccion.Rollback();
                sqlConexion.Close();
                throw e;
            }
        }

        public void deleteCustomer(int customerID, Int64 customerGroupID)
        {

            SqlTransaction myTrasnsaccion;
            sqlConexion.Open();
            myTrasnsaccion = sqlConexion.BeginTransaction();
            SqlCommand cmd;
            try
            {

                cmd = new SqlCommand("delete CustomersByGroups where CustomerID = " + customerID + "and CustomerGroupID = " + customerGroupID, sqlConexion, myTrasnsaccion);

                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("delete Customers where CustomerID = " + customerID, sqlConexion, myTrasnsaccion);

                cmd.ExecuteNonQuery();

                myTrasnsaccion.Commit();
                sqlConexion.Close();
            }
            catch (Exception e)
            {
                myTrasnsaccion.Rollback();
                sqlConexion.Close();
                throw e;
            }
        }

        public void eliminarPoligono(string user, int idPoly)
        {

            SqlTransaction myTrasnsaccion;
            sqlConexion.Open();
            myTrasnsaccion = sqlConexion.BeginTransaction();
            SqlCommand cmd;
            try
            {
                cmd = new SqlCommand(eliminarPoligonoDetalleBD, sqlConexion, myTrasnsaccion);

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@IdPoly", idPoly);
                cmd.Parameters.AddWithValue("@Username", user);
                cmd.ExecuteNonQuery();


                cmd = new SqlCommand(eliminarPoligonoBD, sqlConexion, myTrasnsaccion);

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@IdPoly", idPoly);
                cmd.Parameters.AddWithValue("@Username", user);
                cmd.ExecuteNonQuery();

                myTrasnsaccion.Commit();
                sqlConexion.Close();
            }
            catch (Exception e)
            {
                myTrasnsaccion.Rollback();
                sqlConexion.Close();
                throw e;
            }
        }

        public string actualizarPoligono(string user, int h, string description, StringCollection PolyList)
        {
        /*    float latitude;
            float longitude;
            string[] latLng;
            string poly = "";
            string 

       //     SqlTransaction myTrasnsaccion;
         //   sqlConexion.Open();
          //  myTrasnsaccion = sqlConexion.BeginTransaction();
            SqlCommand cmd;
            try
            {
            //    cmd = new SqlCommand("GeoFence_Insert", sqlConexion, myTrasnsaccion);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@Description", description);
                cmd.ExecuteNonQuery();

                for (int i = 1; i <= PolyList.Count; i++)
                {
                    latLng = PolyList[i - 1].Split('$');
                    latitude = float.Parse(latLng[0]);
                    longitude = float.Parse(latLng[1]);

                //    cmd = new SqlCommand(insertarPoligonoDetalleBD, sqlConexion, myTrasnsaccion);

                    cmd.Parameters.Clear();
                //    cmd.Parameters.AddWithValue("@IdPoly", idPoly);
                    cmd.Parameters.AddWithValue("@Username", user);
                    cmd.Parameters.AddWithValue("@NumPos", i);
                    cmd.Parameters.AddWithValue("@Latitude", latitude);
                    cmd.Parameters.AddWithValue("@Longitude", longitude);

                    cmd.ExecuteNonQuery();

                    if (i == 1)
                    {
                      //  poly = idPoly + "$" + PolyName + "&" + i + "$" + latitude + "$" + longitude;
                    }
                    else
                        poly = poly + "$" + i + "$" + latitude + "$" + longitude;
                }

                //myTrasnsaccion.Commit();
                sqlConexion.Close();
            }
            catch (Exception e)
            {
               // myTrasnsaccion.Rollback();
                sqlConexion.Close();
                throw e;
            }
            return poly;*/
            return "";
        }

        public string GetCustomersGroupsByUsername(string username)
        {
            SqlDataAdapter da;
            DataSet ds = new DataSet();

            try
            {
                sqlConexion.Open();
                da = new SqlDataAdapter(" Select cg.CustomerGroupID, cg.CustomerGroupName " +
                                        " From UsersByCustomersGroups ubcg " +
                                        " inner join CustomersGroups cg " +
                                        " on cg.CustomerGroupID = ubcg.CustomerGroupID " +
                                        " Where ubcg.Username = '" + username + "'" +
                                        " order by cg.CustomerGroupName", sqlConexion);
                da.Fill(ds);

                return ds.GetXml();

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public string GetCustomersByUsernameAndCustomerGroupID(string username, Int64 customerGroupID)
        {             
            SqlDataAdapter da;
            DataSet ds = new DataSet();
            
            try{
                sqlConexion.Open();
                da = new SqlDataAdapter(    " Select c.CustomerID, c.CustomerName, c.Latitude, c.Longitude" +
                                            " From UsersByCustomersGroups ubcg" +
                                            " inner join CustomersByGroups cbg" +
                                            " on ubcg.CustomerGroupID = cbg.CustomerGroupID" +
                                            " inner join Customers c" +
                                            " on c.CustomerID = cbg.CustomerID" +
                                            " inner join CustomersGroups cg" +
                                            " on cg.CustomerGroupID = cbg.CustomerGroupID" +
                                            " Where	cbg.CustomerGroupID = " + customerGroupID + 
                                            " and	ubcg.Username = '" + username + "'" +
                                            " order by CustomerGroupName",sqlConexion); 
                da.Fill(ds);
            
                return ds.GetXml();

            }
            catch (Exception e){
                throw e;
            }
        }

        public string wsGetCustomerByAdmin(string username)
        {
            SqlDataAdapter da;
            DataSet ds = new DataSet();

            try
            {
                sqlConexion.Open();
                da = new SqlDataAdapter(" Select Username, Name, FirstName, LastName " +
                                        " From Users " + /* +
                                        " Where Username = '" + username + "'" +*/
                                        " order by Name", sqlConexion);
                da.Fill(ds);

                return ds.GetXml();

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public string wsGetImeiByUser(string username)
        {
            SqlDataAdapter da;
            DataSet ds = new DataSet();

            try
            {
                sqlConexion.Open();
                da = new SqlDataAdapter(" Select gi.Imei, i.Name, it.Image, td.Latitude, td.Longitude " +
                                        " From UsersByGroups ug " +
                                        " Inner Join GroupsByImei gi " +
                                        " on ug.GroupID = gi.GroupID " +
                                        " Inner Join Imei i " +
                                        " on gi.Imei = i.Imei " +
                                        " Inner Join ImeiType it " +
                                        " on it.ImeiTypeID = i.ImeiTypeID " +
                                        " Inner Join TrackDataLastLocation td " +
                                        " on td.Imei = i.Imei " +
                                        " Where ug.Username = '" + username + "'" +
                                        " order by i.Name", sqlConexion);
                da.Fill(ds);

                return ds.GetXml();

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public string wsGetAllCustomerByUser(string username, int orderColumn )
        {
            SqlDataAdapter da;
            DataSet ds = new DataSet();

            try
            {
                sqlConexion.Open();
                da = new SqlDataAdapter(" Select c.CustomerID, c.CustomerName, cg.CustomerGroupName, c.Latitude, c.Longitude " +
                                        " From UsersByCustomersGroups ucg " +
                                        " Inner Join CustomersGroups cg " +
                                        " on ucg.CustomerGroupID = cg.CustomerGroupID " +
                                        " Inner Join CustomersByGroups cbg " +
                                        " on cbg.CustomerGroupID = cg.CustomerGroupID " +
                                        " Inner Join Customers c " +
                                        " on c.CustomerID = cbg.CustomerID " +
                                        " Where ucg.Username = 'demo' " +
                                        " Order by " + orderColumn + " asc", sqlConexion);
                da.Fill(ds);

                return ds.GetXml();

            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}